/* Subversion Info: $Id: $ */

/* Drop the current Professional Groups table */
PROMPT Dropping table: PROFESSIONAL_GROUPS
set term off
DROP TABLE professional_groups CASCADE constraints;
set term on

/* Drop the current Professional Groups to Personnel bridge table */
PROMPT Dropping table: PROFGRP_PERSONNEL
set term off
DROP TABLE profgrp_personnel CASCADE constraints;
set term on

/* Drop the current Professional Groups PK sequence */
PROMPT Dropping sequence: PROFESSIONAL_GROUPS_PK_SEQ
set term off
DROP SEQUENCE professional_groups_pk_seq;
set term on

/* Professional Groups PK sequence to be used for auto NUMBER PRIMARY KEY sequence */
PROMPT Creating sequence: PROFESSIONAL_GROUPS_PK_SEQ
set term off
CREATE SEQUENCE professional_groups_pk_seq 
INCREMENT BY 1
START WITH 1
NOCACHE;
set term on

/* Create the Professional Groups table */
PROMPT Creating table: PROFESSIONAL_GROUPS
set term off
CREATE TABLE professional_groups
  (
     id   NUMBER(2),
     name VARCHAR2(25) NOT NULL,
     constraint professional_groups_pk primary key (id)
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON ftdbadm.professional_groups TO ftdbuser;
set term on

/* Create the Professional Groups to Personnel bridge table */
PROMPT Creating table: PROFGRP_PERSONNEL
set term off
CREATE TABLE profgrp_personnel
  (
     profgrp_id   NUMBER(2),
     personnel_id NUMBER(3),
     constraint pgp_profgrp_fk foreign key (profgrp_id)
     	references professional_groups(id),
     constraint pgp_personnel_fk foreign key (personnel_id)
     	references personnel(id)
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON ftdbadm.profgrp_personnel TO ftdbuser;
set term on

/* Project insert trigger */
PROMPT Creating trigger: PROFESSIONAL_GROUPS_PK_TRIG
set term off
CREATE OR REPLACE TRIGGER professional_groups_pk_trig 
  before INSERT ON professional_groups
  FOR each ROW
BEGIN
    SELECT professional_groups_pk_seq.nextval
    INTO   :new.id
    FROM   dual;
END;
/
set term on

/* Commit all installation changes */
set term off
COMMIT;
set term on

PROMPT
PROMPT Configuration Complete: PROFESSIONAL_GROUPS
PROMPT